/* Appendix Figure 1 */



#delimit ;
clear all;

local outfile "GWgapr10_desc_byyr";
set more off;








*******************************************************************************;
* Reading in and preparing individual-level IDI data;
*******************************************************************************;


use GWgap_pr_IDI_v4, clear; 

drop if indiv_avfte_hp==.;

* merging in hours worked for HLFS sample;

merge 1:1 snz_uid year using GWgap_pr_HLFS_v4, keep(master match) keepus(hrs_main_HLFS Q_rest_HLFS) nogen;


* creating earnings normalised by ftes or hours worked;

gen Wpm_hp = exp(lnWpm_hp);

gen lnearn_FTE = ln(Wpm_hp/indiv_avfte_hp);
label var lnearn_FTE "Monthy earnings/FTEs at hp pent (ln)";

gen lnearn_hrs = ln(Wpm_hp/hrs_main_HLFS);
label var lnearn_hrs "Monthly earnings/weekly hours at hp pent (ln)";


* creating these earnings variables for desired samples;

gen lnearn_FTE_Q_rest = lnearn_FTE if Q_rest==1;
gen lnearn_FTE_Q_rest_HLFS = lnearn_FTE if Q_rest_HLFS==1;
gen lnearn_hrs_Q_rest_HLFS = lnearn_hrs if Q_rest_HLFS==1;


* collapsing to gender year level;

gen obs = 1 if lnearn_hrs_Q_rest_HLFS<. | (year<2007 & lnearn_FTE_Q_rest<.);

collapse (mean) lnearn_FTE lnearn_FTE_Q_rest lnearn_FTE_Q_rest_HLFS lnearn_hrs_Q_rest_HLFS
	(count) min_obs = obs,
	by(year female);
	
/* use this line to confidentialise observation count;	
grr min_obs, base(3) seed(50124);
*/
* drop this line when using conf code above;
gen rr3min_obs = min_obs;

drop min_obs;


label var lnearn_FTE "Monthy earnings/FTEs at hp pent (ln)";
label var lnearn_FTE_Q_rest "Monthy earnings/FTEs at hp pent (ln), restricted IDI sample";
label var lnearn_FTE_Q_rest_HLFS "Monthy earnings/FTEs at hp pent (ln), restricted HLFS sample";
label var lnearn_hrs "Monthly earnings/weekly hours at hp pent (ln)";
label var rr3min_obs "Minimum number of obesrvations (rr3)";

format lnearn_FTE lnearn_FTE_Q_rest lnearn_FTE_Q_rest_HLFS lnearn_hrs_Q_rest_HLFS %9.3g;
tostring lnearn_FTE lnearn_FTE_Q_rest lnearn_FTE_Q_rest_HLFS lnearn_hrs_Q_rest_HLFS, replace force usedisplay;

sort female year;
export excel using "`outfile'.xlsx", sh("datab") sheetrep first(varl);

